<?php 
Import::appLibs("Configuration");
class PostModel extends Model {
	public function __construct(){
		$this->open(Model::MYSQL);
			$this->calledClass(get_class($this));
	}
	public function queryJoinTable(){
		$query = "SELECT Jobs.id, Jobs.job_categories, Jobs.job_type, Jobs.title, Employers.bussiness as by_company, Jobs.job_type, Jobs.approved,
				Jobs.job_time, Jobs.location, Jobs.expire_date,Posts.id, Posts.emp_id, 
				Posts.post_date, Posts.type, Posts.approve, Posts.created, Posts.job_id
				FROM Jobs
				INNER JOIN Posts ON Jobs.id = Posts.job_id 
				INNER JOIN Employers ON Employers.id = Jobs.emp_id";
		return $query;
	}
	public function DisplayAdmin($query,$page=1,$perpage=20,$search = array(), $sortFields = array() , $sortCriteria = "ASC", $operation = "AND" ){
		$this->open(Model::MYSQL);
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=$sortCriteria===""?"ASC":$sortCriteria;
		$operation=$operation===""?"AND":$operation;
		$query = "$query" ;
		if(isset($search)&&$search!==""){
			$tempSearchFields=array();
			foreach($search as $key=>$val){
				$tempSearchFields[]= " `$key` LIKE '%". $val ."%' " ;
			}
			$query .= " WHERE " . implode(" $operation ",$tempSearchFields);			
		}
		if(!empty($sortFields)){
			$tempSortFields=array();
			foreach($sortFields AS $field ){
				$tempSortFields[]= " `$field` " ;
			}
			$query .= " ORDER BY " . implode(" , ",$tempSortFields) . " " . $sortCriteria . " " ;
		}
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
		}
		return array("query"=>$query,"search"=>$search,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
	public function queryPostApprove($approve){
		$query = "SELECT Jobs.id, Jobs.job_categories, Jobs.job_type, Jobs.title, Employers.bussiness as by_company,
		Jobs.job_time, Jobs.location, Jobs.expire_date,Posts.id, Posts.emp_id,
		Posts.post_date, Posts.type, Posts.approve, Posts.created, Posts.job_id
		FROM Jobs
		INNER JOIN Posts ON Jobs.id = Posts.job_id 
		INNER JOIN Employers ON Employers.id = Jobs.emp_id WHERE Posts.approve= $approve AND";
		return $query;
	}
	public function DisplayAdminWithoutWhere($query,$page=1,$perpage=20,$search = array(), $sortFields = array() , $sortCriteria = "ASC", $operation = "AND" ){
		$this->open(Model::MYSQL);
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=$sortCriteria===""?"ASC":$sortCriteria;
		$operation=$operation===""?"AND":$operation;
		$query = "$query" ;
		if(isset($search)&&$search!==""){
			$tempSearchFields=array();
			foreach($search as $key=>$val){
				$tempSearchFields[]= " `$key` LIKE '%". $val ."%' " ;
			}
			$query .= implode(" $operation ",$tempSearchFields);
		}
		if(!empty($sortFields)){
			$tempSortFields=array();
			foreach($sortFields AS $field ){
				$tempSortFields[]= " `$field` " ;
			}
			$query .= " ORDER BY " . implode(" , ",$tempSortFields) . " " . $sortCriteria . " " ;
		}
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
		}
		return array("query"=>$query,"search"=>$search,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
	public function getALLCategory(){
		$this->open(Model::MYSQL);
		$query = "SELECT id,name,parent_id FROM Categories";
		$result = parent::query($query);
		while ($row = mysql_fetch_object($result)) {
			$datas[]=$row;
		}
		return $datas;
	}
	public function CountCategoriesFunction($parent_id){
		$query = "SELECT id, name, parent_id FROM Categories WHERE parent_id = $parent_id  LIMIT 5" ;
		$this->open(Model::MYSQL);
		$result = parent::query($query);
		$rows = array();
		while ($row = mysql_fetch_object($result)) {
			$query="SELECT Categories.name, count(Jobs.job_categories) as countCate, Categories.`name`, Jobs.job_categories
					FROM Categories INNER JOIN Jobs ON Categories.id = Jobs.job_categories
					WHERE Categories.id = $row->id";
			$sResult = parent::query( $query );
			while($sRow = mysql_fetch_object($sResult))$row->has_child=$sRow;
			$rows[]=$row;
		}
		return isset($rows)?$rows:null;
	}
	public function getCountNameCategory($parent_id){
		$this->open(Model::MYSQL);
		$query ="SELECT Count(Categories.`name`) as countCat FROM Categories WHERE parent_id=$parent_id";
		$result = parent::query($query);
		return mysql_fetch_object($result);
	}
	public function getList($page=1,$perpage=20,$search="",$searchFields=array(),$sortFields=array(),$sortCriteria=""){
		$this->open(Model::MYSQL);
		return parent::getList($page,$perpage,$search,$searchFields,$sortFields,$sortCriteria);
	}
	public function QueryFront($emp_id){
		$query = "SELECT Jobs.id,Jobs.user_id,Jobs.posted_by, Jobs.title, Jobs.posting_date, Jobs.publish, Jobs.post_date, Jobs.job_category, Jobs.job_type, Jobs.approved,
				  Employers.company_name as by_company, Jobs.location, Jobs.deadline,Jobs.post_mode,Jobs.location_category
				  FROM Jobs, Employers WHERE user_id='".$emp_id."' AND Employers.id=Jobs.user_id AND publish='Y'";
		return $query;
	}
	
	public function PaginationFront($query,$page=1,$perpage=20,$search=array(), $sortFields = array() , $sortCriteria = "ASC", $operation = "AND" ){
		$this->open(Model::MYSQL);
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=$sortCriteria===""?"ASC":$sortCriteria;
		$operation=$operation===""?"AND":$operation;
		$query = "$query" ;
		if(isset($search)&&$search!==""){
			$tempSearchFields=array();
			foreach($search as $key=>$val){
				if($val!="")$tempSearchFields[]= " $key LIKE '%". $val ."%' " ;
			}
			$query .= !empty($tempSearchFields)? implode(" $operation ",$tempSearchFields) :"" ;
		}
		if(!empty($sortFields)){
			$tempSortFields=array();
			foreach($sortFields AS $field ){
				$tempSortFields[]= " `$field` " ;
			}
			$query .= " ORDER BY " . implode(" , ",$tempSortFields) . " " . $sortCriteria . " " ;
		}
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
		}
		return array("query"=>$query,"search"=>$search,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
	public function PaginationFrontJob($query,$page=1,$perpage=20,$title="",$cat="",$loc="",$time="", $sortFields = array() , $sortCriteria = "ASC" ){
		$this->open(Model::MYSQL);
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=$sortCriteria===""?"ASC":$sortCriteria;
		 $query = "$query" ;
		//array(""=>$this->get("jobcat"),""=>$this->get("jobtitle"),"location"=>$this->get("joblocation"),"job_type"=>$this->get("jobtype")));
		if(!empty($title)){
		 	$query.= " AND  ((Jobs.title LIKE '%".$title."%') OR (Jobs.posted_by LIKE '%".$title."%'))";
		}
		if(!empty($cat)){
			$query.= " AND job_category LIKE '%".$cat."%'";
		}
		if(!empty($loc)){
			$query.= " AND location_category LIKE '%".$loc."%'";
		}
		if(!empty($time)){
			$query.= " AND job_type LIKE '%".$time ."%'";
		}
		
		if(!empty($sortFields)){
			$tempSortFields=array();
			foreach($sortFields AS $field ){
				$tempSortFields[]= " `$field` " ;
			}
			$query .= " GROUP BY Jobs.id ORDER BY " . implode(" , ",$tempSortFields) . " " . $sortCriteria . " " ;
		}
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
		}
		return array("query"=>$query,"search"=>$query,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
	
	public function getPostedJobs($page=1,$perpage=20,$search="", $searchFields = array() , $where=array() ){
		$this->open(Model::MYSQL);
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=" ASC ";
		$query = " SELECT * FROM Jobs";
		
		if(is_array($where)&&!empty($where)){
			$tempSearchFields=array();
			foreach($where as $key => $val ){
				if($val!=""&&$val!=NULL)$tempSearchFields[]= " $key'$val' " ;
			}
			$query .= !empty($tempSearchFields)?" WHERE " .  implode(" OR ",$tempSearchFields):"";
		}
		
		if(isset($search)&&$search!==""){
			$tempSearchFields=array();
			foreach($searchFields as $field){
				$tempSearchFields[]= " $field LIKE '%". $search ."%' " ;
			}
			$query .= (strpos($query,"WHERE")!==false ? " AND " :" WHERE ") .  implode(" OR ",$tempSearchFields);
		}
		if( strpos($query,"WHERE")!==false ){
			$query .= " AND approved='Y' AND expired='N' AND publish='Y'" ; 
		}else{
			$query .= " WHERE approved='Y' AND expired='N' AND publish='Y'" ;
		}
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
		}
		return array("query"=>$query,"search"=>$search,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
	
	public function delete($where=array(),$deleteSet=false){
		$this->open(Model::MYSQL);
		return parent::delete($where,$deleteSet);
	}
	
	public function save($fields=array()){
		$this->open(Model::MYSQL);
		return parent::save($fields);
	}
	public function version($table="",$field="id",$where=array()){
		$this->open(Model::MYSQL);
		return parent::getFieldValue($table,$field,$where);
	}
	public function edit($fields=array(),$wheres=array()){
		$this->open(Model::MYSQL);
		return parent::update($fields,$wheres);
	}
	public function findObject($where=array()){
		$this->open(Model::MYSQL);
		return $this->findAsObject($where);
	}
	/**
	 * Standard and Premium posting
	 */
	public function GetEmpIdPost($table="",$field="id",$where=array()){
		$this->open(Model::MYSQL);
		return parent::getFieldValue($table,$field,$where);
	}
	public function PostJob(){
		$this->open(Model::MYSQL);
		$query = "SELECT id, emp_id FROM Posts
		ORDER BY id DESC LIMIT 1";
		$result = parent::query($query) ;
		$datas = mysql_fetch_object($result);
		return $datas;
	}
	public function CountPosting($emp_id){
		$this->open(Model::MYSQL);
		$query = "SELECT type FROM Posts WHERE type = '1' and emp_id = $emp_id; ";
		$result = parent::query($query);
		return is_resource($result)?mysql_num_rows($result):null;
	}
	public function CountPostingPoint($emp_id){
		$this->open(Model::MYSQL);
		$query = "SELECT posting_point FROM Memberships WHERE user_id = '" . $emp_id . "'; ";
		$result = parent::query($query);
		$result =  mysql_fetch_object($result);
		return $result->posting_point;
	}
	public function CuttingPoint($field,$where){
		if($field >= 0){
			$this->open(Model::MYSQL);
			$query = "UPDATE Memberships SET posting_point=".$field." WHERE user_id = '".$where."';";
			$result = parent::query($query);
			$querypostingpoint = "SELECT posting_point FROM Memberships WHERE user_id= '".$where."'";
			$result = parent::query($querypostingpoint);
			$result =  mysql_fetch_object($result);
			return intval($result->posting_point);
		}
	}
	/*Multi table with find as object*/
	public function GetFieldWithMultiTable($query, $where=array()){
		$this->open(Model::MYSQL);
		if(is_array($where)&&!empty($where)){
			$query = " $query " ;
			$temp=array();
			foreach($where as $fieldName => $fieldValue ){
				$temp[] = $fieldName . "'" . mysql_real_escape_string( $fieldValue ) . "' " ;
			}
			$query .= " WHERE " . implode( " AND " , $temp ) . " ; " ;
			$result = parent::query($query);
			if($result!==null){
				$rows = $result ;
				$result = array();
				while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
			}
			return $result===null?$result:(empty($result)?null:$result[0]);
		}
	}
	public function getData($emp_id){
		$this->open(Model::MYSQL);
		$query = "select * from Memberships where user_id='".$emp_id."'";
		$result = parent::query($query);
		$datas = mysql_fetch_object($result);
		return $datas;
	}
	public function queryDropdown($id){
		$this->open(Model::MYSQL);
		return parent::query("select category_id,category_name,parent_category_id from Categories where parent_category_id=$id");
	}
	public function QueryEmp(){
		$this->open(Model::MYSQL);
		$query = "SELECT * FROM Employers";
		$result = parent::query($query);
		while ($row = mysql_fetch_object($result)) {
			$datas[]=$row;
		}
		return $datas;
	}
	public function QueryGetField(){
		$query = "SELECT Jobs.id AS jobId, Jobs.user_id, Jobs.title, Employers.bussiness as by_company, Jobs.company_type, 
				Jobs.num_hire,Jobs.contact_person, Jobs.job_time, Jobs.location, Jobs.qualification, 
				Jobs.job_categories, Jobs.industry,
				Jobs.gender, Jobs.age, Jobs.post_date, Jobs.expire_date, Jobs.photo, Jobs.`profile`,
				Jobs.description, Jobs.education, Jobs.competencies, Jobs.apply, Jobs.experience,
				Jobs.`language`, Jobs.marital, Jobs.contact_person, Jobs.address, Jobs.telephone,
				Jobs.email, Jobs.website, Jobs.job_type, Jobs.`status`, Jobs.created, Jobs.version, Jobs.`modify`, Jobs.modify_by,
				Employers.firstname, Employers.lastname, Employers.id AS empId
				FROM
                Employers
				INNER JOIN Jobs ON Employers.id = Jobs.user_id";
		return $query;
	}
	public function GetPostByEmpId($emp_id){
		$this->open(Model::MYSQL);
		$query = "SELECT * FROM Posts WHERE emp_id = $emp_id" ;
		$result = parent::query($query);
		$count = mysql_num_rows($result);
		return $count;
	}
	public function GetPost(){
		$this->open(Model::MYSQL);
		$query = "SELECT * FROM Posts" ;
		$result = parent::query($query);
		$count = mysql_num_rows($result);
		return $count;
	}
	public function GetPostByApprove(){
		$this->open(Model::MYSQL);
		$query = "SELECT * FROM Posts WHERE approve = 1" ;
		$result = parent::query($query);
		while ($row = mysql_fetch_object($result)) {
			$datas[]=$row;
		}
		return $datas;
	}
	/* START HR Cambodia DB OLD */
	
	public function displayhr($page=1,$perpage=20,$search,$sortCriteria=array(),$operation="AND"){
		$this->openhr(Model::MYSQL);
		
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=$sortCriteria===""?"ASC":$sortCriteria;
		$operation=$operation===""?"AND":$operation;
		/*
		$query = "$query" ;
		if(isset($search)&&$search!==""){
			$tempSearchFields=array();
			foreach($search as $key=>$val){
				$tempSearchFields[]= " `$key` LIKE '%". $val ."%' " ;
			}
			$query .= " WHERE " . implode(" $operation ",$tempSearchFields);
		}
		if(!empty($sortFields)){
			$tempSortFields=array();
			foreach($sortFields AS $field ){
				$tempSortFields[]= " `$field` " ;
			}
			$query .= " ORDER BY " . implode(" , ",$tempSortFields) . " " . $sortCriteria . " " ;
		}
		*/
		//$query = "SELECT SQL_CALC_FOUND_ROWS * FROM posts_table where approved='Y' AND expired='N' ORDER BY ( `post_date` ) DESC " ;
		$query = "SELECT * FROM posts_table where approved='Y' AND expired='N' ORDER BY ( `post_date` ) DESC " ;
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false)$result[]=$row;
		}
		return array("query"=>$query,"search"=>$search,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
	
	/* END HR Cambodia DB OLD */
	public function displayJobApp($page=1,$perpage=20,$search = array(), $sortFields = array() , $sortCriteria = "ASC", $operation = "AND" ){
		$this->open(Model::MYSQL);
		$page=$page<1?1:$page;
		$perpage=$perpage<1?20:$perpage;
		$sortCriteria=$sortCriteria===""?"ASC":$sortCriteria;
		$operation=$operation===""?"AND":$operation;
		$query = "SELECT Jobs.* , Employers.company_name , Employers.img_id  FROM Jobs, Employers WHERE Jobs.user_id=Employers.id AND ( Jobs.approved='Y' AND Jobs.expired='N' AND Jobs.publish='Y' ) AND " ;
		if(isset($search)&&$search!==""){
			$tempSearchFields=array();
			foreach($search as $key=>$val){
				$tempSearchFields[]= " `$key` LIKE '%". $val ."%' " ;
			}
			$query .= implode(" $operation ",$tempSearchFields);
		}
		if(!empty($sortFields)){
			$tempSortFields=array();
			foreach($sortFields AS $field ){
				$tempSortFields[]= " `$field` " ;
			}
			$query .= " ORDER BY " . implode(" , ",$tempSortFields) . " " . $sortCriteria . " " ;
		}
		$totalRecords = mysql_num_rows(parent::query($query));
		$totalPages = ceil($totalRecords/$perpage);
		$query .= " LIMIT ".(($page-1)*$perpage)." , ".$perpage." ; " ;
		$result = parent::query($query);
		if($result!==null){
			$rows = $result ;
			$result = array();
			while(($row=mysql_fetch_object($rows))!==false){
				if($row->img_id!=""&&$row->img_id>0){
					$query = "SELECT * FROM Galleries WHERE id = $row->img_id " ;
					$result1 = $this->query ( $query );
					while($row1=mysql_fetch_object($result1))$row->img_id=$row1;
				}
				$result[]=$row;
			}
		}
		return array("query"=>$query,"search"=>$search,"page"=>$page,"perpage"=>$perpage,"totalpages"=>$totalPages,"totalrecords"=>$totalRecords,"records"=>($result===null?array():$result)) ;
	}
}
